﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using _4T.Data;
using CMS.Core.Libs;

namespace _4T.DAL
{
    public class ProfileStatusDAL
    {
        public ProfileStatusDAL() { }

        public int InsertProfileStatus(int profileID, int stage, int status, string proposer, string filter, string verifier,
                                        string approver, string money, string note, string profileType, string profileUploader)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStatusInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileInfoID", SqlDbType.Int).Value = profileID;
                    cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                    cmd.Parameters.Add("@Status", SqlDbType.Bit).Value = status;
                    cmd.Parameters.Add("@Proposer", SqlDbType.VarChar, 50).Value = proposer;
                    cmd.Parameters.Add("@Filter", SqlDbType.VarChar, 50).Value = filter;
                    cmd.Parameters.Add("@Verifier", SqlDbType.VarChar, 50).Value = verifier;
                    cmd.Parameters.Add("@Approver", SqlDbType.VarChar, 50).Value = approver;
                    cmd.Parameters.Add("@Money", SqlDbType.NText).Value = money;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Parameters.Add("@ProfileType", SqlDbType.VarChar, 50).Value = profileType;
                    cmd.Parameters.Add("@ProfileUploader", SqlDbType.VarChar, 50).Value = profileUploader;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public bool UpdateProfileStatus(string profileInfoCode, int stage, int status, string proposer, string filter, string verifier,
                                        string approver, string money, string note, string paymentperson)
        {
            bool result = false;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStatusUpdate";
                    cmd.CommandType = CommandType.StoredProcedure;
                    /*if (stage == 4 && status == 1)
                    {
                        cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = 5;
                        cmd.Parameters.Add("@Status", SqlDbType.Int).Value = 0;
                    }
                    else
                    {*/
                        cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                        cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;
                    //}
                    cmd.Parameters.Add("@Proposer", SqlDbType.VarChar, 50).Value = proposer;
                    cmd.Parameters.Add("@Filter", SqlDbType.VarChar, 50).Value = filter;
                    cmd.Parameters.Add("@Verifier", SqlDbType.VarChar, 50).Value = verifier;
                    cmd.Parameters.Add("@Approver", SqlDbType.VarChar, 50).Value = approver;
                    cmd.Parameters.Add("@Money", SqlDbType.NText).Value = money;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileInfoCode;
                    cmd.Parameters.Add("@PaymentPerson", SqlDbType.VarChar, 50).Value = paymentperson;
                    cmd.Connection = conn;

                    int rowEffect = Convert.ToInt32(cmd.ExecuteScalar());
                    if (rowEffect > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public bool UpdateProfileStatus(string profileCode, int stage, int status, string proposer, string filter, string verifier,
                                        string approver, string money, string note, string paymentPerson, string paymentAmount)
        {
            bool result = false;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStatusUpdate";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                    cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;
                    cmd.Parameters.Add("@Proposer", SqlDbType.VarChar, 50).Value = proposer;
                    cmd.Parameters.Add("@Filter", SqlDbType.VarChar, 50).Value = filter;
                    cmd.Parameters.Add("@Verifier", SqlDbType.VarChar, 50).Value = verifier;
                    cmd.Parameters.Add("@Approver", SqlDbType.VarChar, 50).Value = approver;
                    cmd.Parameters.Add("@Money", SqlDbType.NText).Value = money;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Parameters.Add("@PaymentPerson", SqlDbType.VarChar, 50).Value = paymentPerson;
                    cmd.Parameters.Add("@PaymentAmount", SqlDbType.NText).Value = paymentAmount;
                    cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;

                    int rowEffect = Convert.ToInt32(cmd.ExecuteScalar());
                    if (rowEffect > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public string UpdateProfileStatusWithStringReturn(string profileCode, int stage, int status, string proposer, string filter, string verifier,
                                        string approver, string money, string note)
        {
            string result = string.Empty;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStatusUpdate";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                    cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;
                    cmd.Parameters.Add("@Proposer", SqlDbType.VarChar, 50).Value = proposer;
                    cmd.Parameters.Add("@Filter", SqlDbType.VarChar, 50).Value = filter;
                    cmd.Parameters.Add("@Verifier", SqlDbType.VarChar, 50).Value = verifier;
                    cmd.Parameters.Add("@Approver", SqlDbType.VarChar, 50).Value = approver;
                    cmd.Parameters.Add("@Money", SqlDbType.NText).Value = money;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;

                    int rowEffect = Convert.ToInt32(cmd.ExecuteScalar());
                    if (rowEffect > 0)
                    {
                        result = "success";
                    }
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public IEnumerable<ProfileStatus> GetAllProfileStatus()
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetAllProfileStatus";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new ProfileStatus
                {
                    ProfileCode = n["ProfileCode"] is DBNull ? string.Empty : n["ProfileCode"].ToString(),
                    Filter = n["Filter"] is DBNull ? string.Empty : n["Filter"].ToString(),
                    CreatedDate = n["LastEditTiem"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["LastEditTiem"])
                });
            }
            else
            {
                return new List<ProfileStatus>();
            }
        }

        public ProfileStatus GetProfileStatusByProfileCode(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                ProfileStatus profileStatus = null;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStatusGet";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        profileStatus = GetProfileStatusFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }


                return profileStatus;
            }

            return null;
        }

        public ProfileStatus GetProfileStatusByProfileCodeWithPureData(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                ProfileStatus profileStatus = null;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStatusGetPureData";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        profileStatus = GetProfileStatusFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }


                return profileStatus;
            }

            return null;
        }

        private ProfileStatus GetProfileStatusFromReader(SqlDataReader reader)
        {
            string profileCode = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                profileCode = reader.GetString(1);

            int stage = 0;
            if (reader.GetValue(2) != DBNull.Value)
                stage = reader.GetInt32(2);

            int status = 0;
            if (reader.GetValue(3) != DBNull.Value)
                status = reader.GetInt32(3);

            string proposer = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                proposer = reader.GetString(4);

            string filter = string.Empty;
            if (reader.GetValue(5) != DBNull.Value)
                filter = reader.GetString(5);

            string verifier = string.Empty;
            if (reader.GetValue(6) != DBNull.Value)
                verifier = reader.GetString(6);

            string approver = string.Empty;
            if (reader.GetValue(7) != DBNull.Value)
                approver = reader.GetString(7);

            string amount = string.Empty;
            if (reader.GetValue(8) != DBNull.Value)
                amount = reader.GetString(8);

            string note = string.Empty;
            if (reader.GetValue(9) != DBNull.Value)
                note = reader.GetString(9);

            string patientName = string.Empty;
            if (reader.GetValue(10) != DBNull.Value)
                patientName = reader.GetString(10);

            string paymentPerson = string.Empty;
            if (reader.GetValue(11) != DBNull.Value)
                paymentPerson = reader.GetString(11);

            return new ProfileStatus(profileCode, stage, status, proposer, filter, verifier, approver, amount, note, patientName, paymentPerson);
        }
    }
}